﻿using System.Threading;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;
using Nardax.Data.Properties;
using System;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using System.Threading.Tasks;

namespace Nardax.Data
{
    public abstract class Database<TCommand, TTransaction, TConnection, TDataAdapter> :
        IDatabase<TCommand, TTransaction, TConnection>
        where TCommand : DbCommand, new()
        where TTransaction : DbTransaction
        where TConnection : DbConnection, new()
        where TDataAdapter : DbDataAdapter, new()
    {
        private readonly int _commandTimeout;
        private readonly string _connectionString;

        /// <summary> 
        /// Instanciate a SQL database using the connection string in the config file that can be located 
        /// by the name passed in. 
        /// </summary> 
        protected Database(DatabaseConnectionPolicy databaseConnectionPolicy, RetryPolicy retryPolicy)
        {
            if (databaseConnectionPolicy == null)
            {
                throw new ArgumentException(ExceptionMessages.NullDatabaseConnectionException, "databaseConnectionPolicy");
            }

            if (retryPolicy == null)
            {
                throw new ArgumentException(ExceptionMessages.NullRetryPolicyException, "retryPolicy");
            }

            _connectionString = databaseConnectionPolicy.ConnectionString;
            _commandTimeout = databaseConnectionPolicy.DefaultCommandTimeout;
            RetryPolicy = retryPolicy;
        }

        protected RetryPolicy RetryPolicy { get; private set; }

        /// <summary> 
        /// Creates a new instance of SqlCommand using the name of the stored procedure provided. The command 
        /// is returned with the command type set to StoredProcedure. 
        /// </summary> 
        /// <param name="storedProcedureName">The name of the stored procedure.</param> 
        /// <returns>A new instance of SqlCommand with the command type already set to StoredProcedure.</returns> 
        /// <remarks>It is highly recommended that all calls to this method be wrapped in a using block to ensure that they are disposed
        /// of at the correct time.</remarks>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities"),
         SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
        public TCommand CreateStoredProcedureCommand(string storedProcedureName)
        {
            if (string.IsNullOrWhiteSpace(storedProcedureName))
            {
                throw new ArgumentException(ExceptionMessages.NullEmptyStringException, "storedProcedureName");
            }

            var command = CreateCommandInternal(storedProcedureName, CommandType.StoredProcedure);
            return command;
        }

        /// <summary> 
        /// Creates a new instance of SqlCommand using the command text provided.
        /// </summary> 
        /// <param name="commandText">The SQL string that will be executed when the command is executed.</param> 
        /// <returns>A new instance of SqlCommand with the command type and text configured.</returns> 
        /// <remarks>This method returns a live command that is created from command text that must be checked for sercurity vulnerabilities 
        /// by the caller. It is highly recommended that all calls to this method be wrapped in a using block to ensure that they are disposed
        /// of at the correct time.</remarks>
        [SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
        public TCommand CreateCommand(string commandText)
        {
            if (string.IsNullOrWhiteSpace(commandText))
            {
                throw new ArgumentException(ExceptionMessages.NullEmptyStringException, "commandText");
            }

            return CreateCommandInternal(commandText, CommandType.Text);
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private TCommand CreateCommandInternal(string commandText, CommandType commandType)
        {
            var command = new TCommand();
            command.CommandText = commandText;
            command.CommandTimeout = _commandTimeout;
            command.CommandType = commandType;

            return command;
        }

        /// <summary> 
        /// Executes a non query command against the database. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>Return value from the command.</returns> 
        public int ExecuteNonQuery(TCommand command)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            using (var connection = CreateOpenConnection())
            {
                command.Connection = connection;
                return RetryPolicy.ExecuteAction(() => command.ExecuteNonQuery());
            }
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>Return a task where the result will be the value from the command.</returns> 
        public async Task<int> ExecuteNonQueryAsync(TCommand command)
        {
            return await ExecuteNonQueryAsync(command, CancellationToken.None);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>Return a task where the result will be the value from the command.</returns> 
        public async Task<int> ExecuteNonQueryAsync(TCommand command, CancellationToken cancellationToken)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            using (var connection = await CreateOpenConnectionAsync(cancellationToken))
            {
                command.Connection = connection;

                return await RetryPolicy.ExecuteAsync(async () => await command.ExecuteNonQueryAsync(cancellationToken), cancellationToken);
            }
        }

        /// <summary> 
        /// Executes a non query command within a transaction against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <returns>Return value from the command.</returns>
        /// <remarks>The connection that is used to execute the non query is the connection that is associated with the transaction.</remarks>
        public int ExecuteNonQuery(TCommand command, TTransaction transaction)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }

            command.Connection = transaction.Connection;
            command.Transaction = transaction;

            return RetryPolicy.ExecuteAction(() => command.ExecuteNonQuery());
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command within a transaction against the database. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <returns>Return a task where the result will be the value from the command.</returns> 
        public async Task<int> ExecuteNonQueryAsync(TCommand command, TTransaction transaction)
        {
            return await ExecuteNonQueryAsync(command, transaction, CancellationToken.None);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param>
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>Return a task where the result will be the value from the command.</returns> 
        public async Task<int> ExecuteNonQueryAsync(TCommand command, TTransaction transaction, CancellationToken cancellationToken)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }

            command.Connection = transaction.Connection;
            command.Transaction = transaction;

            return await RetryPolicy.ExecuteAsync(async () => await command.ExecuteNonQueryAsync(cancellationToken), cancellationToken);
        }

        /// <summary> 
        /// Opens a reader on the database using the command passed in. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns> 
        public IDataReader ExecuteReader(TCommand command)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            var connection = CreateOpenConnection();

            try
            {
                command.Connection = connection;
                return RetryPolicy.ExecuteAction(() => command.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteReader"/>. Opens a reader on the database using the command passed in. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns> 
        public async Task<IDataReader> ExecuteReaderAsync(TCommand command)
        {
            return await ExecuteReaderAsync(command, CancellationToken.None);
        }
        
        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteReader"/>. Opens a reader on the database using the command passed in. 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns> 
        public async Task<IDataReader> ExecuteReaderAsync(TCommand command, CancellationToken cancellationToken)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            var connection = await CreateOpenConnectionAsync(cancellationToken);

            try
            {
                command.Connection = connection;
                return await RetryPolicy.ExecuteAsync(async () => await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, cancellationToken), cancellationToken);
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The return result of the scalar command</returns> 
        public object ExecuteScalar(TCommand command)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            using (var connection = CreateOpenConnection())
            {
                command.Connection = connection;
                return RetryPolicy.ExecuteAction(() => command.ExecuteScalar());
            }
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The return result of the scalar command</returns> 
        public async Task<object> ExecuteScalarAsync(TCommand command)
        {
            return await ExecuteScalarAsync(command, CancellationToken.None);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The return result of the scalar command</returns> 
        public async Task<object> ExecuteScalarAsync(TCommand command, CancellationToken cancellationToken)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            using (var connection = await CreateOpenConnectionAsync(cancellationToken))
            {
                command.Connection = connection;
                return await RetryPolicy.ExecuteAsync(async () => await command.ExecuteScalarAsync(cancellationToken), cancellationToken);
            }
        }

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public T ExecuteScalar<T>(TCommand command)
        {
            var scalarValue = ExecuteScalar(command);

            if (typeof(T).IsEnum)
            {
                return (T)Enum.Parse(typeof(T), ChangeType<String>(scalarValue));
            }

            return ChangeType<T>(scalarValue);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public async Task<T> ExecuteScalarAsync<T>(TCommand command)
        {
            return await ExecuteScalarAsync<T>(command, CancellationToken.None);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public async Task<T> ExecuteScalarAsync<T>(TCommand command, CancellationToken cancellationToken)
        {
            var scalarValue = await ExecuteScalarAsync(command, cancellationToken);

            if (typeof(T).IsEnum)
            {
                var enumResult = (T)Enum.Parse(typeof(T), ChangeType<String>(scalarValue));
                return enumResult;
            }

            var result = ChangeType<T>(scalarValue);
            return result;
        }

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public T? ExecuteNullableScalar<T>(TCommand command) where T : struct
        {
            var scalarValue = ExecuteScalar(command);

            if (scalarValue == DBNull.Value)
            {
                return new T?();
            }

            if (typeof(T).IsEnum)
            {
                return (T)Enum.Parse(typeof(T), ChangeType<String>(scalarValue));
            }

            return ChangeType<T>(scalarValue);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public async Task<T?> ExecuteNullableScalarAsync<T>(TCommand command) where T : struct
        {
            return await ExecuteNullableScalarAsync<T>(command, CancellationToken.None);
        }

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary> 
        /// <param name="command">The command to be executed.</param> 
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        public async Task<T?> ExecuteNullableScalarAsync<T>(TCommand command, CancellationToken cancellationToken) where T : struct
        {
            var scalarValue = await ExecuteScalarAsync(command, cancellationToken);

            if (scalarValue == DBNull.Value)
            {
                return new T?();
            }

            if (typeof(T).IsEnum)
            {
                return (T)Enum.Parse(typeof(T), ChangeType<String>(scalarValue));
            }

            return ChangeType<T>(scalarValue);
        }

        /// <summary>
        /// Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        public TConnection CreateOpenConnection()
        {
            TConnection connection = null;

            try
            {
                connection = new TConnection { ConnectionString = _connectionString };

                RetryPolicy.ExecuteAction(() => connection.Open());
            }
            catch
            {
                if (connection != null)
                {
                    connection.Close();
                }

                throw;
            }

            return connection;
        }

        /// <summary>
        /// An asynchronous version of <see cref="CreateOpenConnection"/>. Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        public async Task<TConnection> CreateOpenConnectionAsync()
        {
            return await CreateOpenConnectionAsync(CancellationToken.None);
        }

        /// <summary>
        /// An asynchronous version of <see cref="CreateOpenConnection"/>. Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        public async Task<TConnection> CreateOpenConnectionAsync(CancellationToken cancellationToken)
        {
            TConnection connection = null;

            try
            {
                connection = new TConnection { ConnectionString = _connectionString };

                await RetryPolicy.ExecuteAsync(async () => await connection.OpenAsync(cancellationToken), cancellationToken);
            }
            catch
            {
                if (connection != null)
                {
                    connection.Close();
                }

                throw;
            }

            return connection;
        }

        /// <summary>
        /// Adds or refreshes rows in a specified DataTable
        /// </summary>
        /// <param name="dataTable">The DataTable where the data should be put.</param>
        /// <param name="selectCommand">The command used to get the data from the database.</param>
        /// <returns>The number of rows successfully added to or refreshed in the System.Data.DataSet. This does not include rows 
        /// affected by statements that do not return rows.</returns>
        /// <remarks></remarks>
        public int Fill(DataTable dataTable, TCommand selectCommand)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException("dataTable");
            }

            if (selectCommand == null)
            {
                throw new ArgumentNullException("selectCommand");
            }

            using (var connection = CreateOpenConnection())
            {
                selectCommand.Connection = connection;

                using (var adapter = new TDataAdapter())
                {
                    adapter.SelectCommand = selectCommand;
                    return RetryPolicy.ExecuteAction(() => adapter.Fill(dataTable));
                }
            }
        }

        /// <summary>
        /// Adds or refreshes rows in a specified DataSet
        /// </summary>
        /// <param name="dataSet">The DataSet where the data should be put.</param>
        /// <param name="selectCommand">The command used to get the data from the database.</param>
        /// <returns>The number of rows successfully added to or refreshed in the System.Data.DataSet. This does not include rows 
        /// affected by statements that do not return rows.</returns>
        /// <remarks></remarks>
        public int Fill(DataSet dataSet, TCommand selectCommand)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException("dataSet");
            }

            if (selectCommand == null)
            {
                throw new ArgumentNullException("selectCommand");
            }

            using (var connection = CreateOpenConnection())
            {
                selectCommand.Connection = connection;

                using (var adapter = new TDataAdapter())
                {
                    adapter.SelectCommand = selectCommand;
                    return RetryPolicy.ExecuteAction(() => adapter.Fill(dataSet));
                }
            }
        }

        private static T ChangeType<T>(object value)
        {
            return (T)Convert.ChangeType(value, typeof(T));
        }
    }
}